﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Web;
using MySql.Data.MySqlClient;
using Basicas.Utils;

namespace ProjetoJornada_DAL
{
    public class DAO
    {
        public MySqlConnection Conn { get; set; }
        private static DAO _instance;
         
        /// <summary>
        /// contrutor para carregamento da Connection String
        /// </summary>
        public DAO()
        {

            MySqlConnectionStringBuilder cs = new MySqlConnectionStringBuilder();
            cs.Server = "mysql.cin.ufpe.br";
            cs.Database = "citi_jornada";
            cs.UserID = "citi_jornada";
            cs.Password = "AE6K7s8T";
            cs.UseProcedureBodies = false;
            
            Conn = new MySqlConnection(cs.ConnectionString);    
           
        }

        public static DAO Instance
        {
            get
            {
                if (_instance == null)
                {
                    _instance = new DAO();
                }

                return _instance;
            }        
        }

        /// <summary>
        /// Executa Comandos SQL do tipo Insert,update,delete sem passagem de parametros
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool ExecutaSql(string sql) 
        {
            IDbTransaction trans = null;
            try
            {   MySqlCommand comm = new MySqlCommand(sql, Conn);
               
                Conn.Open();
                trans = Conn.BeginTransaction();

                comm.ExecuteNonQuery();
                
                trans.Commit();
                Conn.Close();

                return true;
            }
            catch (Exception ex)
            {
                if (trans != null)
                {
                    trans.Rollback();
                }

                if (Conn.State==ConnectionState.Open)
                {
                    Conn.Close();
                }
                
                Auxiliar.GravaLog(ex.Message, "ExecutaSql");
                throw ex;
            }
        }

        /// <summary>
        /// Executa Comandos SQL do tipo Stored Procedures
        /// </summary>
        /// <param name="parametros"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool ExecutaSql(MySqlParameter[] parametros,string sql)
        {
            
            IDbTransaction trans = null;  

            try
            {
                MySqlCommand comm = new MySqlCommand(sql, Conn);
                comm.Parameters.AddRange(parametros);
                comm.CommandType = CommandType.StoredProcedure;

                Conn.Open();
                trans = Conn.BeginTransaction();

                comm.ExecuteNonQuery();
                trans.Commit();

                Conn.Close();

                return true;
            }
            catch (Exception ex)
            {
                if (trans != null)
                {
                    trans.Rollback();
                }               

                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }

                Auxiliar.GravaLog(ex.Message, "ExecutaSql");
                throw ex;
            }
        }

        /// <summary>
        /// Executa Comandos SQL de leitura
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet ExecutaLeitura(string sql)
        {
            DataSet ds = new DataSet();
            IDbTransaction trans = null;
            try
            {           
                MySqlDataAdapter da = new MySqlDataAdapter(sql, Conn);
                Conn.Open();
                trans = Conn.BeginTransaction();

                da.Fill(ds);

                trans.Commit();

                Conn.Close();               

                return ds;
            }
            catch (Exception ex)
            {
                if (trans != null)
                {
                    trans.Rollback();
                }
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }

                Auxiliar.GravaLog(ex.Message, "ExecutaSql");
                throw ex;
            }
        }

        public long ExecutaEscalar(MySqlParameter[] parametros, string sql)
        {
            long retorno = 0;
            IDbTransaction trans = null;

            try
            {
                MySqlCommand comm = new MySqlCommand(sql, Conn);

                comm.Parameters.AddRange(parametros);

                comm.CommandType = CommandType.StoredProcedure;

                Conn.Open();
                trans = Conn.BeginTransaction();
                
                comm.ExecuteNonQuery();
                                      
                retorno = comm.LastInsertedId;

                trans.Commit();

                Conn.Close();

                return retorno;
            }
            catch (Exception ex)
            {
                if(trans != null)
                {
                    trans.Rollback();
                }
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }

                Auxiliar.GravaLog(ex.Message, "ExecutaSql");
                throw ex;
            }
        }

        public object ExecutaFuncao(MySqlParameter[] parametros, string nomeDaFuncao)
        {
            MySqlTransaction trans = null;

            try
            {
                Conn.Open();
                MySqlCommand comm = new MySqlCommand(nomeDaFuncao, Conn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddRange(parametros);
                              
                trans = Conn.BeginTransaction();

                comm.ExecuteNonQuery();

                trans.Commit();
                Conn.Close();

                MySqlParameter returnValue = parametros[parametros.Length - 1];

                return returnValue.Value;
            
            }
            catch(Exception ex)
            {
                if (trans != null)
                {
                    trans.Rollback();
                }

                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }

                Auxiliar.GravaLog(ex.Message, "ExecutaFuncao");
                throw ex;            
            }      
        
        }
        
        public MySqlDataReader ExecutaLeituraProcedure(MySqlParameter[] parametros, string nomeDaProcedure)
        {

            try
            {
                Conn.Open();
                MySqlCommand comm = new MySqlCommand(nomeDaProcedure, Conn);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddRange(parametros);

                MySqlDataReader dr = comm.ExecuteReader();

                return dr;


            }
            catch (Exception ex)
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }

                Auxiliar.GravaLog(ex.Message, "ExecutaLeituraProcedure");
                throw ex;
            }       
        
        }

        public MySqlDataReader ExecutaLeituraProcedure(string nomeDaProcedure)
        {
            try
            {
                Conn.Open();
                MySqlCommand comm = new MySqlCommand(nomeDaProcedure, Conn);
                comm.CommandType = CommandType.StoredProcedure;
                MySqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                return dr;
            }
            catch (Exception ex)
            {
                Auxiliar.GravaLog(ex.Message, "ExecutaLeituraProcedure");
                throw ex;
            }

        }

        public bool FecharConexao()
        {
            if (Conn.State == ConnectionState.Open)
            {
                Conn.Close();
            }

            return Conn.State == ConnectionState.Closed;
        }
    }
}
